﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Script.Serialization;
using MinistryStandardPlatformEntity;
using MinistryStandardPlatformWeb.Models.ReportCenter;

namespace MinistryStandardPlatformWeb.Controllers.Reports
{
    [MenuAuthorized("42")]
    public class TripStatisticController : Controller
    {
        private MSPDataContext context = new MSPDataContext();
        //
        // GET: /TripStatistic/

        public ActionResult Index()
        {
            return View();
        }
        [HttpPost]
        public ActionResult GetData(string startTime, string endTime, List<string> vehicleNums)
        {
            if (!string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime) && vehicleNums != null && vehicleNums.Count != 0)
            {
                string sql = "select vehicleNum mVehicleNum, "
                           + "SUBSTRING(t.outStartRegTime,0,11) mTime, "
                           + "cast(count(*) as varchar(10)) mTrip, "
                           + "r.endregname mSiteName "
                           + "from TranTaskListDetail t, TranTaskList r "
                           + "where t.trantasklistid=r.tasklistid and t.outStartRegTime >= '" + startTime + "' and t.outStartRegTime <= '" + endTime + "' and ";
                for (int i = 0; i < vehicleNums.Count;i++ )
                {
                    if (i == 0)
                    {
                        sql += "(";
                    }
                    sql += "t.vehiclenum='" + vehicleNums[i] + "' or ";
                    if (i == vehicleNums.Count - 1)
                    {
                        sql += "1=0) ";
                    }
                }
                sql += "group by r.endregname, t.vehicleNum, SUBSTRING(t.outStartRegTime,0,11)";
                List<TripStatistic> list = this.context.ExecuteQuery<TripStatistic>(sql).ToList();
                var json = new
                {
                    total = list.Count,
                    rows = list
                };
                var serializer = new JavaScriptSerializer();
                serializer.MaxJsonLength = Int32.MaxValue;
                return Content(serializer.Serialize(json), "application/json");
            }
            return null;
        }


        [HttpPost]
        [MenuAuthorized("1", Order = 1)]
        public ActionResult ExportExcel()
        {
            List<TripStatistic> list = CommonFunctions.JsonStrToObject<List<TripStatistic>>(Request.Form["data"]);
            var sbHtml = new System.Text.StringBuilder();
            sbHtml.Append("<table border='1' cellspacing='0' cellpadding='0'>");
            sbHtml.Append("<tr>");
            var lstTitle = new List<string> { "序号","车牌号", "SIM卡号", "所属单位","工地名称","日期","趟次", "绩效" };
            foreach (var item in lstTitle)
            {
                sbHtml.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item);
            }
            sbHtml.Append("</tr>");

            for (int i = 0; i < list.Count; i++)
            {
                sbHtml.Append("<tr>");
                sbHtml.AppendFormat("<td>{0}</td>", i + 1);
                sbHtml.AppendFormat("<td>{0}</td>", list[i].mVehicleName);
                sbHtml.AppendFormat("<td style='vnd.ms-excel.numberformat:@;'>{0}</td>", list[i].mSim);
                sbHtml.AppendFormat("<td>{0}</td>", list[i].mDepart);
                sbHtml.AppendFormat("<td>{0}</td>", list[i].mSiteName);
                sbHtml.AppendFormat("<td>{0}</td>", list[i].mTime);
                sbHtml.AppendFormat("<td>{0}</td>", list[i].mTrip);
                sbHtml.AppendFormat("<td>{0}</td>", list[i].mPerformance);
                sbHtml.Append("</tr>");
            }
            sbHtml.Append("</table>");

            //第一种:使用FileContentResult
            byte[] fileContents = System.Text.Encoding.Default.GetBytes(sbHtml.ToString());
            return File(fileContents, "application/ms-excel", "趟次统计.xls");

            ////第二种:使用FileStreamResult
            //var fileStream = new MemoryStream(fileContents);
            //return File(fileStream, "application/ms-excel", "fileStream.xls");

            ////第三种:使用FilePathResult
            ////服务器上首先必须要有这个Excel文件,然会通过Server.MapPath获取路径返回.
            //var fileName = Server.MapPath("~/Files/fileName.xls");
            //return File(fileName, "application/ms-excel", "fileName.xls");
            //return null;
        }
    }
}
